package com.cool4j.generator.db;

import com.cool4j.generator.bean.ColumnBean;
import com.cool4j.generator.bean.DatabaseBean;
import com.cool4j.generator.bean.TableBean;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库信息助手类
 *
 * @author Frank create on 16/1/8 21:06
 */
public class DBInfoHelper {

    private Connection conn = null;
    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/test";
    private String username = "root";
    private String password = "123456";

    public DBInfoHelper(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }

    /**
     * 使用 JDBC 获取数据库连接
     * @return - 数据库连接
     */
    public Connection jdbcConnection() {
        try {
            // 装载驱动包类
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("装载驱动包出现异常,请校验!");
            e.printStackTrace();
        }
        try {
            // 获取数据库连接
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("连接数据库异常!");
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取表信息
     *
     * @param tableNamePattern - 表名
     * @return - 表信息
     * @throws SQLException - SQL 异常
     */
    public DatabaseBean tableInfo(String tableNamePattern) throws SQLException {
        DatabaseBean databaseBean = new DatabaseBean();

        // 表队列
        List<TableBean> tableList = new ArrayList<>();

        // 打开数据库连接
        conn = jdbcConnection();

        // 获取数据库信息
        DatabaseMetaData metaData = conn.getMetaData();

        // 保存数据库产品信息
        databaseBean.setProductName(metaData.getDatabaseProductName());

        // 获取数据库表
        ResultSet rs = metaData.getTables(null, null, tableNamePattern, new String[] {"TABLE", "VIEW"});

        while (rs.next()) {
            TableBean table = new TableBean();
            table.setName(rs.getString("TABLE_NAME"));
            table.setComment(rs.getString("REMARKS"));
            table.setSchemaName(rs.getString(1));

            System.out.println(rs.getString("TABLE_CAT"));
            System.out.println(rs.getString("TABLE_SCHEM"));
            System.out.println(rs.getString("TABLE_NAME"));
            System.out.println(rs.getString("TABLE_TYPE"));
            System.out.println(rs.getString("REMARKS"));
            System.out.println(rs.getString("TYPE_CAT"));
            System.out.println(rs.getString("TYPE_SCHEM"));
            System.out.println(rs.getString("TYPE_NAME"));
            System.out.println(rs.getString("SELF_REFERENCING_COL_NAME"));
            System.out.println(rs.getString("REF_GENERATION"));

            // 设置列信息
            ResultSet columnInfo = metaData.getColumns(null, null, table.getName(), null);
            ColumnBean column;
            while (columnInfo.next()) {
                column = new ColumnBean();
                column.setName(columnInfo.getString("COLUMN_NAME"));
                column.setType(Integer.parseInt(columnInfo.getString("DATA_TYPE")));
                String remarks = columnInfo.getString("REMARKS");
                if (remarks.length() < 1) remarks = ".";
                // 列中文名称
                column.setComment(remarks.substring(0, remarks.indexOf(".")));
                // 列详细注释
                column.setCommentDetail(remarks.substring(remarks.indexOf("."), remarks.length()));
                column.setAutoIncrement("YES".equals(columnInfo.getString("IS_AUTOINCREMENT")));
                column.setNullable("YES".equals(columnInfo.getString("IS_NULLABLE")));
                // 添加列到表中
                table.getColumnList().add(column);
            }

            // 设置主键列
            ResultSet primarySet = metaData.getPrimaryKeys(null, null, table.getName());
            while (primarySet.next()) {
                if (primarySet.getString("COLUMN_NAME") != null) {
                    for (int i = 0; i < table.getColumnList().size(); i++) {
                        ColumnBean columnBean = table.getColumnList().get(i);
                        if (columnBean.getName().equals(primarySet.getString("COLUMN_NAME"))) {
                            columnBean.setPrimary(true);
                        }
                    }
                }
            }

            // 设置外键列
            ResultSet foreignSet = metaData.getImportedKeys(null, null, table.getName());
            while (foreignSet.next()) {
                for (int i = 0; i < table.getColumnList().size(); i++) {
                    ColumnBean columnBean = table.getColumnList().get(i);
                    if (columnBean.getName().equals(foreignSet.getString("FKCOLUMN_NAME"))) {
                        columnBean.setForeignKey(true);
                    }
                }
            }
            tableList.add(table);
        }
        databaseBean.setTableList(tableList);
        return databaseBean;
    }

}
